package com.servlet.dao;

import com.servlet.bean.Student;
import com.servlet.bean.User;
import com.servlet.utils.JDBCHelper;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @Title: StudentDao
 * @Description:
 * @Auther: py
 * @Version: 1.0
 * @create 2023/12/26 14:15
 */
public class StudentDao {

    /**
     * 返回所有的学生信息
     * @return
     */
    public List<Student> queryStus() throws SQLException {
        Connection connection = JDBCHelper.getConnection();
        String sql = "select * from t_student";
        QueryRunner queryRunner = new QueryRunner();
        List<Student> stus = queryRunner.query(connection,sql,new BeanListHandler<Student>(Student.class));
        return stus;
    }

    /**
     * 通过id检索学生
     * @param id
     * @return
     * @throws SQLException
     */
    public Student queryStuById(String id) throws SQLException {
        Connection connection = JDBCHelper.getConnection();
        String sql = "select * from t_student where id=?;";
        QueryRunner queryRunner = new QueryRunner();
        Student stu = queryRunner.query(connection,sql,new BeanHandler<Student>(Student.class),id);
        return stu;
    }

    /**
     * 添加学生
     * @param stu
     * @throws SQLException
     */
    public void addStu(Student stu) throws SQLException {
        Connection connection = JDBCHelper.getConnection();
        String sql = "insert into t_student(id,name,image,age,score,comedate,remark) values(?,?,?,?,?,?,?);";
        QueryRunner queryRunner = new QueryRunner();
        queryRunner.update(connection,sql,stu.getId(),stu.getName(),
                stu.getImage(),stu.getAge(),stu.getScore(),stu.getComedate(),stu.getRemark());
    }

    /**
     * 修改学生
     * @param stu
     * @throws SQLException
     */
    public void editStu(Student stu) throws SQLException {
        Connection connection = JDBCHelper.getConnection();
        String sql = "update t_student set name=?,image=?,age=?,score=?,comedate=?,remark=? where id=?;";
        QueryRunner queryRunner = new QueryRunner();
        queryRunner.update(connection,sql,stu.getName(),
                stu.getImage(),stu.getAge(),stu.getScore(),stu.getComedate(),stu.getRemark(),stu.getId());
    }

    /**
     * 删除学生
     * @param id
     * @throws SQLException
     */
    public void deleteStuById(String id) throws SQLException {
        Connection connection = JDBCHelper.getConnection();
        String sql = "delete from t_student where id=?;";
        QueryRunner queryRunner = new QueryRunner();
        queryRunner.update(connection,sql,id);
    }
}
